/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ConnectMySQLServer;

import Model.Entity.Account;
import Model.Entity.Book;
import Model.Entity.Category;
import Model.Entity.ListData;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;
import com.mysql.jdbc.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author CUONGTROC
 */
public class DataAccess {

    public DataAccess() {
    }
    private final static String QUERY_ALL_CATEGORY = "SELECT * FROM `hiredbooks`.`category`;";
    private final static String QUERY_ALL_BOOK = "SELECT * FROM `hiredbooks`.`bookdetails`ORDER BY Publication DESC;";
    private final static String QUERY_ADD_ACCOUNT = "insert into register(Email,Pass,Passconfirm,FirstName,LastName,PhoneNo) values(?,?,?,?,?,?);";
    private final static String QUERY_SELECT_ACCOUNT = "SELECT * FROM `hiredbooks`.`register` WHERE Email=? AND Pass=?;";
    private final static String QUERY__BookDetails="SELECT * FROM `hiredbooks`.`bookdetails` WHERE BookID=?;";
    private final static String QUERY_SEARCH_BOOK ="SELECT * FROM `hiredbooks`.`bookdetails` WHERE BookName Like ? ORDER BY Publication DESC;";
    private final static String QUERY_GET_BOOK_IN_CATEGORY="SELECT * FROM `hiredbooks`.`bookdetails` WHERE CategoryID=? ORDER BY Publication DESC;";
    public ArrayList<Category> getAllCategory() throws Exception {
        try {
            ConnectionDatabase connectData = new ConnectionDatabase();
            Connection conn = connectData.getConnection();
            Statement stm = (Statement) conn.createStatement();
            ResultSet rs = stm.executeQuery(QUERY_ALL_CATEGORY);
            ArrayList<Category> categories = new ArrayList<Category>();
            while (rs.next()) {
                Category category = new Category();
                category.setId(rs.getString("CategoryID"));
                category.setName(rs.getString("CategoryName"));
                categories.add(category);
            }
//            ListData<Category> listCategory = new ListData<Category>();
//            listCategory.setData(categories);
            rs.close();
            return categories;
        } catch (Exception e) {
            throw e;
        }
    }
    public ArrayList<Book> getAllBook() throws Exception{
        try {
            ConnectionDatabase connectData = new ConnectionDatabase();
            Connection conn = connectData.getConnection();
            Statement stm = (Statement) conn.createStatement();
            ResultSet rs = stm.executeQuery(QUERY_ALL_BOOK);
            ArrayList<Book> books = new ArrayList<Book>();
            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getString("BookID"));
                book.setCategory(rs.getString("CategoryID"));
                book.setName(rs.getString("BookName"));
                book.setAuthor(rs.getString("Author"));
                book.setIsbn_10(rs.getString("ISBN_10"));
                book.setIsbn_13(rs.getString("ISBN_13"));
                book.setEdition(rs.getInt("Edition"));
                book.setImages(rs.getString("Images"));
                book.setDescription(rs.getString("Desciption"));
                book.setPrice(rs.getFloat("Price"));
                books.add(book);
            }
//            ListData<Book> listBook = new ListData<Book>();
//            listBook.setData(books);
            rs.close();
            return books;
        } catch (Exception e) {
            throw e;
        }
    }
    public Account getAccount(Account account)throws Exception{
        Account acc = null;
        try {
            ConnectionDatabase connectData = new ConnectionDatabase();
            Connection conn = connectData.getConnection();
            PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(QUERY_SELECT_ACCOUNT);
            pstmt.setString(1, account.getEmail());
            pstmt.setString(2, account.getPass());
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                acc = new Account();
                acc.setEmail(rs.getString("Email"));
                acc.setPass(rs.getString("Pass"));
                acc.setPassconfirm(rs.getString("Passconfirm"));
                acc.setFirstname(rs.getString("FirstName"));
                acc.setLastname(rs.getString("LastName"));
                acc.setPhone(rs.getString("PhoneNo"));
            }
        } catch (Exception ex) {
            throw ex;
        }
        return acc;
    }
    public Account addAccount(Account account) throws Exception{
        Account acc = null;
        try {
            ConnectionDatabase connectData = new ConnectionDatabase();
            Connection conn = connectData.getConnection();
            PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(QUERY_ADD_ACCOUNT);
            pstmt.setString(1, account.getEmail());
            pstmt.setString(2, account.getPass());
            pstmt.setString(3, account.getPassconfirm());
            pstmt.setString(4, account.getFirstname());
            pstmt.setString(5, account.getLastname());
            pstmt.setString(6, account.getPhone());
            pstmt.executeUpdate();
        } catch (Exception ex) {
            throw ex;
        }
        return acc;
    }
    public ArrayList<Book> getBooksInCategory(String CategoryID) throws Exception {
        try {
            ConnectionDatabase connectData = new ConnectionDatabase();
            Connection conn = connectData.getConnection();
            PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(QUERY_GET_BOOK_IN_CATEGORY);
            pstmt.setString(1, CategoryID);
            ResultSet rs = pstmt.executeQuery();
            ArrayList<Book> books = new ArrayList<Book>();
            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getString("BookID"));
                book.setCategory(rs.getString("CategoryID"));
                book.setName(rs.getString("BookName"));
                book.setAuthor(rs.getString("Author"));
                book.setIsbn_10(rs.getString("ISBN_10"));
                book.setIsbn_13(rs.getString("ISBN_13"));
                book.setPublication(rs.getDate("Publication"));
                book.setEdition(rs.getInt("Edition"));
                book.setImages(rs.getString("Images"));
                book.setDescription(rs.getString("Desciption"));
                book.setPrice(rs.getFloat("Price"));
                books.add(book);
            }
//            ListData<Book> bookList = new ListData<Book>();
//            bookList.setData(books);
            rs.close();
            conn.close();
            return books;
        } catch (Exception ex) {
            throw ex;
        }

    }
    public Book getBookDetails(String BookID) throws Exception {
        try {
            ConnectionDatabase connectData = new ConnectionDatabase();
            Connection conn = connectData.getConnection();
            PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(QUERY__BookDetails);
            pstmt.setString(1, BookID);
            ResultSet rs = pstmt.executeQuery();
            Book book = null;
            if (rs.next()) {
                book = new Book();
                book.setId(rs.getString("BookID"));
                book.setCategory(rs.getString("CategoryID"));
                book.setName(rs.getString("BookName"));
                book.setAuthor(rs.getString("Author"));
                book.setIsbn_10(rs.getString("ISBN_10"));
                book.setIsbn_13(rs.getString("ISBN_13"));
                book.setPublication(rs.getDate("Publication"));
                book.setEdition(rs.getInt("Edition"));
                book.setImages(rs.getString("Images"));
                book.setDescription(rs.getString("Desciption"));
                book.setPrice(rs.getFloat("Price"));
            }
            rs.close();
            conn.close();
            return book;
        } catch (Exception ex) {
            throw ex;
        }
    }
    public ArrayList<Book> searchBook(String keyWord) throws Exception {
        try {
            ConnectionDatabase connectData = new ConnectionDatabase();
            Connection conn = connectData.getConnection();
            if (keyWord.equals("")) {
                return getAllBook();
            }
            keyWord = "%" + keyWord + "%";
            PreparedStatement pstmt = (PreparedStatement) conn.prepareStatement(QUERY_SEARCH_BOOK);
            pstmt.setString(1, keyWord);
//            pstmt.setString(2, keyWord);
            ResultSet rs = pstmt.executeQuery();
            ArrayList<Book> books = new ArrayList<Book>();
            while (rs.next()) {
                Book book = new Book();
                book.setId(rs.getString("BookID"));
                book.setCategory(rs.getString("CategoryID"));
                book.setName(rs.getString("BookName"));
                book.setAuthor(rs.getString("Author"));
                book.setIsbn_10(rs.getString("ISBN_10"));
                book.setIsbn_13(rs.getString("ISBN_13"));
                book.setPublication(rs.getDate("Publication"));
                book.setEdition(rs.getInt("Edition"));
                book.setImages(rs.getString("Images"));
                book.setDescription(rs.getString("Desciption"));
                book.setPrice(rs.getFloat("Price"));
                books.add(book);
            }
//            ListData<Book> listBook = new ListData<Book>();
//            listBook.setData(books);
            rs.close();
            conn.close();
            return books;
        } catch (Exception ex) {
            throw ex;
        }

    }
    
}
